package com.lagou.dao;

import com.lagou.domain.Student;
import com.lagou.util.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 */
public class StudentDao {

    /**
     * 获取学生的个数
     * @return 返回学生的个数
     */
    public int getTotal(){
        int total = 0;
        String sql = "select count(*) from student";
        Connection connection = DruidUtils.getConnection();
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);

            while (rs.next()){
                total += rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }

    /**
     * 添加学生信息
     * @param student
     */
    public void add(Student student){
        String sql = "insert into student (stname,sex,birthday) values(?,?,?)";
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,student.getStname());
            ps.setString(2,student.getSex());
            ps.setDate(3,new java.sql.Date(student.getBirthday().getTime()));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 删除学生信息
     * @param id 学生的id
     */
    public void delete(int id){
        String sql = "delete from student where id = ?";
        Connection connection = DruidUtils.getConnection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新学生信息
     * @param student 学生类
     */
    public void update(Student student){
        String sql = "update student set stname = ?, sex = ?, birthday = ? where id = ? ";
        Connection connection = DruidUtils.getConnection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setString(1,student.getStname());
            ps.setString(2,student.getSex());
            ps.setDate(3,new java.sql.Date(student.getBirthday().getTime()));
            ps.setInt(4,student.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据Id 或者学生类
     * @param id
     * @return
     */
    public Student get(int id){

        String sql = "SELECT * FROM student WHERE id = " + id;
        Connection connection = DruidUtils.getConnection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            if(rs.next()){
                int stu_id = rs.getInt(1);
                String name = rs.getString(2);
                String sex = rs.getString(3);
                Date date = rs.getDate(4);
                Student student = new Student(stu_id,name,sex,date);
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return null;
    }

    /**
     * 返回全部的学生
     * @return
     */
    public List<Student> list(){
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student ORDER BY id";
        Connection connection = DruidUtils.getConnection();

        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()){
                int stu_id = rs.getInt(1);
                String name = rs.getString(2);
                String sex = rs.getString(3);
                Date date = rs.getDate(4);
                Student student = new Student(stu_id,name,sex,date);
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
